/***********************************************************************************************************************************
 *    Name: sp_ReplMonitorHelpSubscription.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: 
**/
USE DistributionDB;
--DROP TABLE #ReplMonitorHelpSubscription;
SET ANSI_NULL_DFLT_OFF OFF;
SET ANSI_NULL_DFLT_ON  ON;
CREATE TABLE #ReplMonitorHelpSubscription (

	Status							INT,			--	Examines the status of all the replication Agents associated with the Publication, and returns the highest status found in the following order:
													--	6 = Failed
													--	5 = Retrying
													--	2 = Stopped
													--	4 = Idle
													--	3 = In progress
													--	1 = Started
	Warning							INT,			--	Maximum Threshold warning generated by a Subscription belonging to the Publication, which can be the logical OR result of one or more of these values.
													--	1 = expiration – a Subscription to a transactional Publication has Not been Synchronized within the retention period Threshold.
													--	2 = Latency - the time taken to replicate data from a transactional Publisher to the Subscriber exceeds the Threshold, in seconds.
													--	4 = Mergeexpiration - a Subscription to a Merge Publication has Not been Synchronized within the retention period Threshold.
													--	8 = MergefastRunDuration - the time taken to complete Synchronization of a Merge Subscription exceeds the Threshold, in seconds, over a fast network connection.
													--	16 = MergeslowRunDuration - the time taken to complete Synchronization of a Merge Subscription exceeds the Threshold, in seconds, over a slow or dial-up network connection.
													--	32 = MergefastRunspeed – the delivery rate for rows during Synchronization of a Merge Subscription has failed to maintain the Threshold rate, in rows per second, over a fast network connection.
													--	64 = MergeslowRunspeed – the delivery rate for rows during Synchronization of a Merge Subscription has failed to maintain the Threshold rate, in rows per second, over a slow or dial-up network connection.
	Subscriber						SYSNAME Null,	--	Is the Name of the Subscriber.
	Subscriber_DB					SYSNAME Null,	--	Is the Name of the database used for the Subscription.
	Publisher_DB					SYSNAME Null,	--	Is the Name of the Publication database.
	Publication						SYSNAME Null,	--	Is the Name of a Publication.
	Publication_Type				INT,			--	Is the Type of Publication, which can be one of these values:
													--	0 = Transactional Publication
													--	1 = Snapshot Publication
													--	2 = Merge Publication
	SubType							INT,			--	Is the subscription type, which can be one of the following values:
													--	0 = Push
													--	1 = Pull
													--	2 = Anonymous
	Latency							INT,			--	The highest Latency, in seconds, for data changes propagated by the Log Reader or Distribution Agents for a transactional Publication.
	LatencyThreshold				INT,			--	Is the maximum Latency for the transactional Publication above which a warning is raised.
	AgentNotRunning					INT,			--	Is the length of time, in hours, during which the Agent has Not Run.
	AgentNotRunningThreshold		INT,			--	Is the length of time, in hours, that the Agent has Not Run before a warning is raised.
	TimeToExpiration				INT,			--	Is the length of time, in hours, before the Subscription expires if Not Synchronized.
	ExpirationThreshold				INT,			--	Is the time, in hours, before the Subscription expires that a warning is raised.
	Last_DistSync					DATETIME,		--	Is the datetime that the Distribution Agent last ran.
	Distribution_AgentName			SYSNAME Null,	--	Is the Name of the Distribution Agent Job for the Subscription to a transactional Publication.
	MergeAgentName					SYSNAME Null,	--	Is the Name of the Merge Agent Job for the Subscription to a Merge Publication.
	MergeSubscriptionFriendlyName	SYSNAME Null,	--	Is the friendly Name given to the Subscription.
	MergeAgentLocation				SYSNAME Null,	--	Is the Name of the server on which the Merge Agent Runs.
	MergeConnectionType				INT,			--	Connection used when Synchronizing a Subscription to a Merge Publication, which can be one of the following values:
													--	1 = local area network (LAN)
													--	2 = dial-up network connection
													--	3 = Web Synchronization.
	MergePerformance				INT,			--	Performance of the last Synchronization compared to all Synchronizations for the Subscription, which is based on the delivery rate of the last Synchronization divided by the average of all previous delivery rates.
	MergeRunSpeed					FLOAT,			--	Is the delivery rate of the last Synchronization for the Subscription.
	MergeRunDuration				INT,			--	Is the length of time to complete the last Synchronization of the Subscription.
	MonitorRanking					INT,			--	Is the ranking value used to order the Subscriptions in the result set, and can be one of these values:
													--	For a transactional Publication:
													--	60 = Error
													--	56 = Warning: performance critical
													--	52 = Warning: expiring soon or expired
													--	50 = Warning: Subscription uninitialized
													--	40 = Retrying failed command
													--	30 = Not Running (success)
													--	20 = Running (starting, Running, or idle)
													--	For a Merge Publication:
													--	60 = Error
													--	56 = Warning: performance critical
													--	54 = Warning: long-Running Merge
													--	52 = Warning: expiring soon
													--	50 = Warning: Subscription uninitialized
													--	40 = Retrying failed command
													--	30 = Running (starting, Running, or idle)
													--	20 = Not Running (success)
	DistributionAgentJobID			BINARY(16),		--	ID of the Distribution Agent Job for Subscriptions to a transactional Publication.
	MergeAgentJobID					BINARY(16),		--	ID of the Merge Agent Job for Subscriptions to a Merge Publication.
	DistributionAgentID				INT,			--	ID of the Distribution Agent Job for the Subscription.
	DistributionAgentProfileID		INT,			--	ID of the Agent profile used by the Distribution Agent.
	MergeAgentID					INT,			--	ID of the Merge Agent Job for the Subscription.
	MergeAgentProfileID				INT,			--	ID of the Agent profile used by the Merge Agent.
	LogReaderAgentName				NVARCHAR(128),
	Publisher						NVARCHAR(128)

);

EXECUTE sys.sp_ReplMonitorHelpSubscription @publication_type= 0, @mode= 4;
INSERT INTO #ReplMonitorHelpSubscription EXECUTE sys.sp_ReplMonitorHelpSubscription @publication_type= 0, @mode= 4;
SELECT * FROM #ReplMonitorHelpSubscription